﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using LuxuryResort.Entity;

namespace LuxuryResort.DAO
{
    public class StaffDAO : IStaffDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.Utils.LRMPConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public UserEntity UserAuthenticate(string userName, string passWord)
        {
            var user = new UserEntity();

            // Check for invalid userName.
            // userName must not be null and must be between 1 and 15 characters.
            if ((string.IsNullOrEmpty(userName)) || (userName.Length > 50))
            {
                Trace.WriteLine("[ValidateUser] Input validation of userName failed.");
                return null;
            }

            // Check for invalid passWord.
            // passWord must not be null and must be between 1 and 25 characters.
            if ((string.IsNullOrEmpty(passWord)) || (passWord.Length > 50))
            {
                Trace.WriteLine("[ValidateUser] Input validation of passWord failed.");
                return null;
            }

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd = new SqlCommand(
                    "SELECT * FROM [dbo].[Staff] WHERE [Username]=@userName AND [Password]=@passWord", _conn);
                _cmd.Parameters.Add("@userName", SqlDbType.VarChar, 50);
                _cmd.Parameters["@userName"].Value = userName;
                _cmd.Parameters.Add("@passWord", SqlDbType.VarChar, 50);
                _cmd.Parameters["@passWord"].Value = passWord;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    user.SID = Guid.Parse(_dr["SID"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Role = _dr["UserRole"].ToString();
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[ValidateUser] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return user;
        }

        public List<ReservationEntity> RetrieveReservationAll()
        {
            var reservationEntityList = new List<ReservationEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[Reservation]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var reservationEntity = new ReservationEntity
                    {
                        RSID = Guid.Parse(_dr["RSID"].ToString()),
                        RMID = Guid.Parse(_dr["RMID"].ToString()),
                        ReservationCode = _dr["ReservationCode"].ToString(),
                        CustomerName = _dr["CustomerName"].ToString(),
                        Quantity = Int32.Parse(_dr["Quantity"].ToString()),
                        PaymentMethod = _dr["PaymentMethod"].ToString(),
                        CreatedBy = Guid.Parse(_dr["CreatedBy"].ToString()),
                        CreatedAt = DateTime.Parse(_dr["CreatedAt"].ToString())
                    };

                    reservationEntityList.Add(reservationEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Reservation] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return reservationEntityList;
        }

        public List<ReservationRoomEntity> RetrieveReservationRoomByRevervationId(Guid reservationId)
        {
            var reservationRoomEntityList = new List<ReservationRoomEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[ReservationRoom] WHERE [RSID] = @RSID", _conn);

                _cmd.Parameters.Add("@RSID", SqlDbType.UniqueIdentifier);
                _cmd.Parameters["@RSID"].Value = reservationId;

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var reservationRoomEntity = new ReservationRoomEntity
                    {
                        ID = Guid.Parse(_dr["ID"].ToString()),
                        RSID = Guid.Parse(_dr["RSID"].ToString()),
                        RMID = Guid.Parse(_dr["RMID"].ToString()),
                        CheckIn = DateTime.Parse(_dr["CheckIn"].ToString()),
                        CheckOut = DateTime.Parse(_dr["CheckOut"].ToString()),
                        CreatedBy = Guid.Parse(_dr["CreatedBy"].ToString()),
                        CreatedAt = DateTime.Parse(_dr["CreatedAt"].ToString())
                    };

                    reservationRoomEntityList.Add(reservationRoomEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Reservation Room] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return reservationRoomEntityList;
        }

        public List<RoomEntity> RetrieveAvailableRoom()
        {
            var roomEntityList = new List<RoomEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[Room] WHERE [IsAvailable] = 1", _conn);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var roomEntity = new RoomEntity
                    {
                        RMID = Guid.Parse(_dr["RMID"].ToString()),
                        RoomNo = Int32.Parse(_dr["RoomNo"].ToString()),
                        RoomType = _dr["RoomType"].ToString(),
                        IsAvailable = bool.Parse(_dr["IsAvailable"].ToString()),
                        PriceUnit = double.Parse(_dr["PriceUnit"].ToString()),
                    };

                    roomEntityList.Add(roomEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Reservation Room] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return roomEntityList;
        }

        public List<FoodBeverageEntity> RetrieveFoodAll()
        {
            var foodBeverageEntityList = new List<FoodBeverageEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[FoodBeverage] WHERE [Type] = 'Food'", _conn);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var foodBeverageEntity = new FoodBeverageEntity
                    {
                        FID = Guid.Parse(_dr["FID"].ToString()),
                        Name = _dr["Name"].ToString(),
                        Type = _dr["Type"].ToString(),
                        PriceUnit = double.Parse(_dr["PriceUnit"].ToString())
                    };

                    foodBeverageEntityList.Add(foodBeverageEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Reservation Room] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return foodBeverageEntityList;
        }

        public List<FoodBeverageEntity> RetrieveBeverageAll()
        {
            var foodBeverageEntityList = new List<FoodBeverageEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[FoodBeverage] WHERE [Type] = 'Beverage'", _conn);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var foodBeverageEntity = new FoodBeverageEntity
                    {
                        FID = Guid.Parse(_dr["FID"].ToString()),
                        Name = _dr["Name"].ToString(),
                        Type = _dr["Type"].ToString(),
                        PriceUnit = double.Parse(_dr["PriceUnit"].ToString())
                    };

                    foodBeverageEntityList.Add(foodBeverageEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Reservation Room] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return foodBeverageEntityList;
        }

        public ReservationEntity RetrieveReservationByReservationCode(string reservationCode)
        {
            var reservationEntity = new ReservationEntity();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[Reservation] WHERE [ReservationCode] = @ReservationCode",
                    _conn);

                _cmd.Parameters.Add("@ReservationCode", SqlDbType.VarChar, 20);
                _cmd.Parameters["@ReservationCode"].Value = reservationCode;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    reservationEntity.RSID = Guid.Parse(_dr["RSID"].ToString());
                    reservationEntity.RMID = Guid.Parse(_dr["RMID"].ToString());
                    reservationEntity.ReservationCode = _dr["ReservationCode"].ToString();
                    reservationEntity.CustomerName = _dr["CustomerName"].ToString();
                    reservationEntity.Quantity = Int32.Parse(_dr["Quantity"].ToString());
                    reservationEntity.PaymentMethod = _dr["PaymentMethod"].ToString();
                    reservationEntity.CreatedBy = Guid.Parse(_dr["CreatedBy"].ToString());
                    reservationEntity.CreatedAt = DateTime.Parse(_dr["CreatedAt"].ToString());
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Reservation Room] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return reservationEntity;
        }
    }
}